package com.by.dao;

import com.by.entity.Tb;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Component("tbDao")
public class TbDaoImpl implements TbDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public static final RowMapper rowMapper = new ParameterizedRowMapper<Tb>() {
        public Tb mapRow(ResultSet rs, int i) throws SQLException {
            Tb t = new Tb();
            t.setId(rs.getInt("id"));
            t.setUser_name(rs.getString("user_name"));
            t.setCourse(rs.getString("course"));
            t.setScore(rs.getString("score"));
            t.setDeptid(rs.getString("deptid"));
            return t;
        }
    };

    @Override
    public List<Tb> getAllTbs() {
        //List<Tb> list = new ArrayList<Tb>();
        /*try {
            Connection conn = dataSource.getConnection();
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select * from tb");
            while(rs.next()){
                Tb t = new Tb();
                t.setId(rs.getInt(1));
                t.setUser_name(rs.getString(2));
                t.setCourse(rs.getString(3));
                t.setScore(rs.getString(4));
                list.add(t);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }*/
        String sql = "select * from tb";
        List<Tb> list = jdbcTemplate.query(sql,rowMapper);
        return list;
    }

    @Override
    public List<Tb> getTbsByCond(Tb tb) {
        StringBuilder sbu = new StringBuilder("select * from tb where 1=1");
        List list = new ArrayList();
        if (tb != null){
            if (tb.getUser_name() != null && tb.getUser_name().length()>0){
                sbu.append(" and user_name like ?");
                list.add("%"+tb.getUser_name()+"%");
            }
            if (tb.getDeptid() != null){
                sbu.append(" and deptid = ?");
                list.add(tb.getDeptid());
            }
        }
        Object[] params = list.toArray();
        return jdbcTemplate.query(sbu.toString(),params,rowMapper);
    }

    @Override
    public int savaTb(Tb tb) {
        String sql = "insert into tb(user_name,course,score,deptid) values(?,?,?,?)";
        Object[] params = {tb.getUser_name(), tb.getCourse(), tb.getScore(), tb.getDeptid()};
        return jdbcTemplate.update(sql, params);
    }

    @Override
    public Tb getTbById(int id) {
        String sql = "select * from tb where id = ?";
        List<Tb> list = jdbcTemplate.query(sql, rowMapper, id);
        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public int updateTb(Tb tb) {
        return 0;
    }

    @Override
    public int dalTb(Tb tb) {
        return 0;
    }
}
